
********************************
***************Generates Table 2
********************************

 cd C:\Users\mjaremski\Desktop\ReplicationFile\Data\

******Count each bank's number of respondents
 use "NYBS+.dta", clear
 keep if yr==1915
 preserve
 forvalues i=1/8 {
	keep bankNYCorr`i' locationNYCorr`i'
	rename bankNYCorr`i' bank
	rename locationNYCorr`i' location 
	save temp`i', replace
	restore
	preserve
	}
 restore 
 clear
 use temp1.dta, clear
 forvalues i=2/8 {
	append using temp`i'.dta
	}
  gen corr=1
 drop if bank==""
 collapse (sum) corr, by(bank location)
 save corr.dta, replace

******Obtain city-level population number for min capital calculation
 insheet using "NYCities.csv", clear
 drop county
 save temp.dta, replace

*******Create Variables and generate averages
 use "NYBS+.dta", clear

 joinby using corr.dta, unmatched(master)
 drop _merge

 replace corr=0 if corr==.
 bysort bankid: egen endyr=max(yr)

 drop if beginyr>1913
 drop if endyr<1921

 drop if deposits==0 | loans==0 | cash==0
 drop if corresp==0

 gen pop=ln(totpop1920)
 gen urban=urb1920/totpop1920
 gen nyshare=nyccorr/corresp
 gen relvalue=assets/(fedmembassetsnearby)

 egen loanswing=mean(Loan_Swing_34_percent), by(bankid)
 rename Loan_Swing_34_percent1914 loanswing1914

 drop if yr>1920
 drop if assets1914==.

 gen distResCity=distNY

 gen dfrom1914=duefrombanks1914/assets1914
 gen dueto1914=dueothers1914/assets1914
 gen duetotal1914=(dueothers1914+duefrombanks1914)
 gen duetototal1914=dueothers1914/duetotal1914

 replace location="New York" if location=="Brooklyn"

 expand 2
 bysort bankid yr: gen count=_n 

 gen type=""
 replace type="1.Big NYC" if corr>0 & countycode==610 & count==1
 replace type="3.Small NYC" if corr==0 & countycode==610 & count==1
 replace type="2.Big NYS" if corr>0 & countycode~=610 & count==1
 replace type="4.Small NYS" if corr==0 & countycode~=610 & count==1
 replace type="5.Member" if count==2 & fedmembever==1
 replace type="6.Non-Member" if count==2 & fedmembever==0
   
 bysort bankid count: egen countyrs=total(fedmemb)

 keep if yr==1915

 gen banks=1 
 gen timeuntilfed=6-countyrs

 replace location=upper(location)

 joinby state stateicp countycode location using temp.dta, unmatched(master)
 drop _merge
 
 egen mincapital=min(capital), by(bankid)
 replace mincap=25000 if mincap==.

 gen nomincap=0
 replace nomincap=1 if mincapital>=mincap

 collapse (sum) banks (mean) fedmembever timeuntilfed trust chmemb distResCity relvalue totpop1920 urban corresp nyshare corr duefrombanks1914 dfrom1914 dueothers1914 dueto1914 duetototal1914 assets1914 loas1914 loanswing1914 loanswing nomincap, by(type)

 save Table2.dta, replace
 export excel using "Table2", replace




